<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=9"/>
<meta name="generator" content="Doxygen 1.8.20"/>
<meta name="viewport" content="width=device-width, initial-scale=1"/>
<title>libxlsxwriter: Working with Formulas</title>
<link href="tabs.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="dynsections.js"></script>
<link href="search/search.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="search/searchdata.js"></script>
<script type="text/javascript" src="search/search.js"></script>
<link href="doxygen.css" rel="stylesheet" type="text/css" />
<link href="customdoxygen.css" rel="stylesheet" type="text/css"/>
</head>
<body>
<div id="top"><!-- do not remove this div, it is closed by doxygen! -->
<div id="titlearea">
<table cellspacing="0" cellpadding="0">
 <tbody>
 <tr style="height: 56px;">
  <td id="projectalign" style="padding-left: 0.5em;">
   <div id="projectname">libxlsxwriter
   </div>
  </td>
 </tr>
 </tbody>
</table>
</div>
<!-- end header part -->
<!-- Generated by Doxygen 1.8.20 -->
<script type="text/javascript">
/* @license magnet:?xt=urn:btih:cf05388f2679ee054f2beb29a391d25f4e673ac3&amp;dn=gpl-2.0.txt GPL-v2 */
var searchBox = new SearchBox("searchBox", "search",false,'Search');
/* @license-end */
</script>
<script type="text/javascript" src="menudata.js"></script>
<script type="text/javascript" src="menu.js"></script>
<script type="text/javascript">
/* @license magnet:?xt=urn:btih:cf05388f2679ee054f2beb29a391d25f4e673ac3&amp;dn=gpl-2.0.txt GPL-v2 */
$(function() {
  initMenu('',true,false,'search.php','Search');
  $(document).ready(function() { init_search(); });
});
/* @license-end */</script>
<div id="main-nav"></div>
<!-- window showing the filter options -->
<div id="MSearchSelectWindow"
     onmouseover="return searchBox.OnSearchSelectShow()"
     onmouseout="return searchBox.OnSearchSelectHide()"
     onkeydown="return searchBox.OnSearchSelectKey(event)">
</div>

<!-- iframe showing the search results (closed by default) -->
<div id="MSearchResultsWindow">
<iframe src="javascript:void(0)" frameborder="0" 
        name="MSearchResults" id="MSearchResults">
</iframe>
</div>

</div><!-- top -->
<div class="PageDoc"><div class="header">
  <div class="headertitle">
<div class="title">Working with Formulas </div>  </div>
</div><!--header-->
<div class="contents">
<div class="toc"><h3>Table of Contents</h3>
<ul><li class="level1"><a href="#ww_formulas_non_us">Non US Excel functions and syntax</a></li>
<li class="level1"><a href="#ww_formulas_future">Formulas added in Excel 2010 and later</a></li>
<li class="level1"><a href="#ww_formulas_errors">Dealing with formula errors</a></li>
<li class="level1"><a href="#ww_formulas_results">Formula Results</a></li>
</ul>
</div>
<div class="textblock"><p>In general a formula in Excel can be used directly in the <a class="el" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb" title="Write a formula to a worksheet cell.">worksheet_write_formula()</a> function:</p>
<div class="fragment"><div class="line"><a class="code" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a>(worksheet, 0, 0, <span class="stringliteral">&quot;=10*B1 + C1&quot;</span>, NULL);</div>
</div><!-- fragment --><div class="image">
<img src="working_with_formulas1.png" alt=""/>
</div>
<p>However, there are a few potential issues and differences that the user should be aware of. These are explained in the following sections.</p>
<h1><a class="anchor" id="ww_formulas_non_us"></a>
Non US Excel functions and syntax</h1>
<p>Excel stores formulas in the format of the US English version, regardless of the language or locale of the end-user's version of Excel. Therefore all formula function names written using libxlsxwriter must be in English:</p>
<div class="fragment"><div class="line"><span class="comment">// The following formula syntax is okay.</span></div>
<div class="line"><a class="code" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a>(worksheet, 0, 0, <span class="stringliteral">&quot;=SUM(1, 2, 3)&quot;</span>, NULL);</div>
<div class="line"> </div>
<div class="line"><span class="comment">// The following formula syntax is in French. Will cause error on load.</span></div>
<div class="line"><a class="code" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a>(worksheet, 0, 1, <span class="stringliteral">&quot;=SOMME(1, 2, 3)&quot;</span>, NULL);</div>
</div><!-- fragment --><p>Also, formulas must be written with the US style separator/range operator which is a comma (not semi-colon). Therefore a formula with multiple values should be written as follows:</p>
<div class="fragment"><div class="line"><span class="comment">// The following formula syntax is okay.</span></div>
<div class="line"><a class="code" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a>(worksheet, 0, 0, <span class="stringliteral">&quot;=SUM(1, 2, 3)&quot;</span>, NULL);</div>
<div class="line"> </div>
<div class="line"><span class="comment">// The following formula use semi-colons. Will cause error on load.</span></div>
<div class="line"><a class="code" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a>(worksheet, 0, 1, <span class="stringliteral">&quot;=SUM(1; 2; 3)&quot;</span>, NULL);</div>
</div><!-- fragment --><p>If you have a non-English version of Excel you can use the following multi-lingual <a href="http://en.excel-translator.de/language/">Formula Translator</a> to help you convert the formula. It can also replace semi-colons with commas.</p>
<h1><a class="anchor" id="ww_formulas_future"></a>
Formulas added in Excel 2010 and later</h1>
<p>Excel 2010 and later versions added functions which weren't defined in the original file specification. These functions are referred to by Microsoft as "Future Functions". Examples of these functions are <code>ACOT</code>, <code>CHISQ.DIST.RT</code> , <code>CONFIDENCE.NORM</code>, <code>STDEV.P</code>, <code>STDEV.S</code> and <code>WORKDAY.INTL</code>.</p>
<p>When written using <code><a class="el" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb" title="Write a formula to a worksheet cell.">worksheet_write_formula()</a></code> these functions need to be fully qualified with a <code>_xlfn.</code> (or other) prefix as they are shown the list below. For example:</p>
<div class="fragment"><div class="line"><a class="code" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a>(worksheet, 0, 0, <span class="stringliteral">&quot;=_xlfn.STDEV.S(B1:B10)&quot;</span>, NULL);</div>
</div><!-- fragment --><p>They will appear without the prefix in Excel:</p>
<div class="image">
<img src="working_with_formulas2.png" alt=""/>
</div>
<p>The following list is taken from <a href="http://msdn.microsoft.com/en-us/library/dd907480%28v=office.12%29.aspx">MS XLSX extensions documentation on future functions</a>.</p>
<table class="markdownTable">
<tr class="markdownTableHead">
<th class="markdownTableHeadNone">Future Functions   </th></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.ACOT</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.ACOTH</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.AGGREGATE</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.ARABIC</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.BASE</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.BETA.DIST</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.BETA.INV</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.BINOM.DIST</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.BINOM.DIST.RANGE</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.BINOM.INV</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.BITAND</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.BITLSHIFT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.BITOR</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.BITRSHIFT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.BITXOR</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.CEILING.MATH</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.CEILING.PRECISE</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.CHISQ.DIST</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.CHISQ.DIST.RT</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.CHISQ.INV</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.CHISQ.INV.RT</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.CHISQ.TEST</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.COMBINA</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.CONCAT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.CONFIDENCE.NORM</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.CONFIDENCE.T</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.COT</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.COTH</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.COVARIANCE.P</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.COVARIANCE.S</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.CSC</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.CSCH</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.DAYS</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.DECIMAL</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>ECMA.CEILING</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.ERF.PRECISE</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.ERFC.PRECISE</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.EXPON.DIST</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.F.DIST</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.F.DIST.RT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.F.INV</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.F.INV.RT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.F.TEST</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.FILTERXML</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.FLOOR.MATH</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.FLOOR.PRECISE</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.FORECAST.ETS</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.FORECAST.ETS.CONFINT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.FORECAST.ETS.SEASONALITY</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.FORECAST.ETS.STAT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.FORECAST.LINEAR</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.FORMULATEXT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.GAMMA</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.GAMMA.DIST</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.GAMMA.INV</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.GAMMALN.PRECISE</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.GAUSS</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.HYPGEOM.DIST</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.IFNA</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.IFS</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.IMCOSH</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.IMCOT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.IMCSC</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.IMCSCH</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.IMSEC</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.IMSECH</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.IMSINH</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.IMTAN</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.ISFORMULA</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>ISO.CEILING</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.ISOWEEKNUM</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.LOGNORM.DIST</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.LOGNORM.INV</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.MAXIFS</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.MINIFS</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.MODE.MULT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.MODE.SNGL</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.MUNIT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.NEGBINOM.DIST</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>NETWORKDAYS.INTL</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.NORM.DIST</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.NORM.INV</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.NORM.S.DIST</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.NORM.S.INV</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.NUMBERVALUE</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.PDURATION</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.PERCENTILE.EXC</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.PERCENTILE.INC</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.PERCENTRANK.EXC</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.PERCENTRANK.INC</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.PERMUTATIONA</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.PHI</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.POISSON.DIST</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.QUARTILE.EXC</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.QUARTILE.INC</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.QUERYSTRING</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.RANK.AVG</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.RANK.EQ</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.RRI</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.SEC</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.SECH</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.SHEET</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.SHEETS</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.SKEW.P</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.STDEV.P</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.STDEV.S</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.SWITCH</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.T.DIST</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.T.DIST.2T</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.T.DIST.RT</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.T.INV</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.T.INV.2T</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.T.TEST</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.TEXTJOIN</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.UNICHAR</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.UNICODE</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.VAR.P</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.VAR.S</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.WEBSERVICE</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.WEIBULL.DIST</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>WORKDAY.INTL</code>   </td></tr>
<tr class="markdownTableRowEven">
<td class="markdownTableBodyNone"><code>_xlfn.XOR</code>   </td></tr>
<tr class="markdownTableRowOdd">
<td class="markdownTableBodyNone"><code>_xlfn.Z.TEST</code>   </td></tr>
</table>
<h1><a class="anchor" id="ww_formulas_errors"></a>
Dealing with formula errors</h1>
<p>If there is an error in the syntax of a formula it is usually displayed in Excel as <code>#NAME</code>?. Alternatively you may get a warning from Excel when the file is loaded. If you encounter an error like this you can debug it as follows:</p>
<ol type="1">
<li>Ensure the formula is valid in Excel by copying and pasting it into a cell. Note, this should be done in Excel and <b>not</b> other applications such as OpenOffice or LibreOffice since they may have slightly different syntax.</li>
<li>Ensure the formula is using comma separators instead of semi-colons, see <a class="el" href="working_with_formulas.html#ww_formulas_non_us">Non US Excel functions and syntax</a> above.</li>
<li>Ensure the formula is in English, see <a class="el" href="working_with_formulas.html#ww_formulas_non_us">Non US Excel functions and syntax</a> above.</li>
<li>Ensure that the formula doesn't contain an Excel 2010+ future function as listed above (<a class="el" href="working_with_formulas.html#ww_formulas_future">Formulas added in Excel 2010 and later</a>). If it does then ensure that the correct prefix is used.</li>
</ol>
<p>Finally if you have completed all the previous steps and still get a <code>#NAME</code>? error you can examine a valid Excel file to see what the correct syntax should be. To do this you should create a valid formula in Excel and save the file. You can then examine the XML in the unzipped file.</p>
<p>The following shows how to do that using Linux <code>unzip</code> and `libxml's <a href="http://xmlsoft.org/xmllint.html">xmllint</a> to format the XML for clarity: </p><pre class="fragment">$ unzip myfile.xlsx -d myfile
$ xmllint --format myfile/xl/worksheets/sheet1.xml | grep '&lt;f&gt;'

        &lt;f&gt;SUM(1, 2, 3)&lt;/f&gt;
</pre><h1><a class="anchor" id="ww_formulas_results"></a>
Formula Results</h1>
<p>Libxlsxwriter doesn't calculate the result of a formula and instead stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.</p>
<p>This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don't have a facility to calculate formulas will only display the 0 results. Examples of such applications are Excel Viewer, PDF Converters, and some mobile device applications.</p>
<p>If required, it is also possible to specify the calculated result of the formula using the <code>result</code> parameter for :func:<code><a class="el" href="worksheet_8h.html#af474c01f7ec8ac1a01206c9c4a5867af" title="Write a formula to a worksheet cell with a user defined numeric result.">worksheet_write_formula_num()</a></code>:</p>
<div class="fragment"><div class="line"><a class="code" href="worksheet_8h.html#af474c01f7ec8ac1a01206c9c4a5867af">worksheet_write_formula_num</a>(worksheet, 0, 0, <span class="stringliteral">&quot;=2+2&quot;</span>, NULL, 4);</div>
</div><!-- fragment --><p>Next: <a class="el" href="working_with_dates.html">Working with Dates and Times</a> </p>
</div></div><!-- contents -->
</div><!-- PageDoc -->
<div class="ttc" id="aworksheet_8h_html_ae57117f04c82bef29805ec3eabc219bb"><div class="ttname"><a href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a></div><div class="ttdeci">lxw_error worksheet_write_formula(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format)</div><div class="ttdoc">Write a formula to a worksheet cell.</div></div>
<div class="ttc" id="aworksheet_8h_html_af474c01f7ec8ac1a01206c9c4a5867af"><div class="ttname"><a href="worksheet_8h.html#af474c01f7ec8ac1a01206c9c4a5867af">worksheet_write_formula_num</a></div><div class="ttdeci">lxw_error worksheet_write_formula_num(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format, double result)</div><div class="ttdoc">Write a formula to a worksheet cell with a user defined numeric result.</div></div>
<!-- HTML footer for doxygen 1.8.20-->
<!-- start footer part -->
<hr class="footer"/><address class="footer"><small>
Copyright 2014-2020 John McNamara.
Generated by&#160;<a href="http://www.doxygen.org/index.html"><img class="footer" src="doxygen.svg" width="104" height="31" alt="doxygen"/></a> 1.8.20
</small></address>
</body>
</html>
